﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Aga.ProofOfConcepts
{
    class DBManager
    {
        public string ConnectionString { get; set; }

        internal bool TableExists(string tableName)
        {
            int? count = 0;

            // Creates a connection
            using (SqlConnection connection = new SqlConnection(this.ConnectionString))
            {
                // Creates a command
                SqlCommand command = connection.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;

                // Counts the records from sys objects that corresponds to tables named whatever is on 'tableName' variable
                command.CommandText = String.Format( "SELECT Count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}]') AND type in (N'U')", tableName);

                // Opens the connection
                connection.Open();

                // Executes the command
                count = command.ExecuteScalar() as int?;
            }

            return (count.HasValue && count.Value == 1);
        }

        internal void CreateTable(TableSchema table)
        {
            var script = GenerateCreateTableScript(table);

            ExecuteNonQuery(script);
        }

        internal void DropTable(TableSchema table)
        {
            var script = GenerateDropTableScript(table);

            ExecuteNonQuery(script);
        }

        private void ExecuteNonQuery(string script)
        {
            using (SqlConnection connection = new SqlConnection(this.ConnectionString))
            {
                SqlCommand command = connection.CreateCommand();

                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = script;

                connection.Open();

                command.ExecuteNonQuery();
            }
        }

        internal static string GenerateDropTableScript(TableSchema table)
        {
            StringBuilder script = new StringBuilder();

            script.AppendFormat("DROP TABLE [{0}]\n", table.Name);

            return script.ToString();
        }

        internal static string GenerateCreateTableScript(TableSchema table)
        {
            StringBuilder script = new StringBuilder();
            
            script.AppendFormat("CREATE TABLE [{0}](\n", table.Name);

            foreach (var field in table.Fields){

                script.AppendFormat("[{0}] ", field.Name); // Name
                script.AppendFormat("[{0}] ", field.Type); // Type

                switch (field.Type){
                    case System.Data.SqlDbType.Int: // Identity
                        if (field.Identity)
                            script.Append("IDENTITY(1,1)");
                        break;
                    case System.Data.SqlDbType.NVarChar:
                    {
                        script.AppendFormat("({0}) ", field.Length);
                        break;
                    }
                    case System.Data.SqlDbType.Decimal:
                    {
                        script.AppendFormat("({0},{1}) ", field.Precision, field.Scale);
                        break;
                    }
                }

                if (!field.Null)
                    script.Append("NOT ");
                script.Append("NULL,\n");
            }

            if (table.Key.Count > 0)
            {
                script.AppendFormat("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED (\n", table.Name);

                foreach (var key in table.Key)
                {
                    script.AppendFormat("[{0}] ASC", key.Name);

                    if ((table.Key.IndexOf(key) + 1) < table.Key.Count) // It isn't the last key
                        script.Append(",\n");
                }
            }

            script.AppendLine(") ON [PRIMARY] ) ON [PRIMARY]");

            return script.ToString();
        }

    }
}
